﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;
using System.Data;
using TRPOP.Config;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using TRPOP.Common;

namespace TRPOP.ComSqlDAL
{
    /// <summary>
    /// 
    /// </summary>
    public class CommonDAL : ICommonDAL
    {
        /// <summary>
        /// 拼装单表搜索信息的SQL查询语句
        /// </summary>
        /// <param name="intTopn"></param>
        /// <param name="strCondition"></param>
        /// <param name="strOrderby"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public string strbuildSqlForList(int intTopn, string strCondition, string strOrderby, string strTableName)
        {
            StringBuilder strBuildSql = new StringBuilder(" SELECT ");
            if (intTopn > 0)
            {
                strBuildSql.Append(" TOP " + intTopn);
            }
            strBuildSql.Append("  *  FROM  " + strTableName + "  WHERE 1=1 ");
            if (string.IsNullOrEmpty(strCondition) == false)
            {
                strBuildSql.Append(strCondition);
            }
            if (string.IsNullOrEmpty(strOrderby) == false)
            {
                strBuildSql.Append(" ORDER BY " + strOrderby);
            }
            return strBuildSql.ToString();
        }

        #region GetDataSet 方法
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strSqlString">执行的sql</param>
        /// <param name="strConn">SQL连接语句</param>
        /// <returns></returns>
        public DataSet GetDataSetByReader(string strSqlString, string strConn)
        {
            DataTable oTable = GetDatatablebyReader(strSqlString, strConn);
            DataSet oDataSet = new DataSet();
            oDataSet.Merge(oTable);
            return oDataSet;
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strSqlString">执行的sql</param>
        /// <param name="strSqlConn">SQL连接语句</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSqlString, string strSqlConn)
        {
            DataSet oDataSet;
            SqlDatabase odbDataBase = new SqlDatabase(strSqlConn);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            try
            {
                oDataSet = odbDataBase.ExecuteDataSet(odbCommand);
                CloseConnection(odbCommand);//关闭数据库连接
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oDataSet;
        }

        /// <summary>
        /// 通过 SqlDatabase  DbCommand 获取数据集
        /// </summary>
        /// <param name="oSqlDatabase"></param>
        /// <param name="oDbCommand"></param>
        /// <returns></returns>
        public DataSet GetDataSet(SqlDatabase oSqlDatabase, DbCommand oDbCommand)
        {
            int recordCount = 0;
            return GetDataSet(oSqlDatabase, oDbCommand, out recordCount);
        }
        /// <summary>
        /// 通过 SqlDatabase  DbCommand 获取数据集
        /// 并且返回记录数
        /// </summary>
        /// <param name="oSqlDatabase"></param>
        /// <param name="oDbCommand"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public DataSet GetDataSet(SqlDatabase oSqlDatabase, DbCommand oDbCommand, out int recordCount)
        {
            DataSet ds = null;
            recordCount = 0;
            try
            {
                using (ds = oSqlDatabase.ExecuteDataSet(oDbCommand))
                {
                    if (oDbCommand.Parameters.Contains("@RecordCount"))
                        recordCount = int.Parse(oDbCommand.Parameters["@RecordCount"].Value.ToString());
                    else
                        recordCount = ds.Tables[0].Rows.Count;
                }
                CloseConnection(oDbCommand);//关闭数据库连接
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            recordCount = (recordCount > 0) ? recordCount : 0;
            return ds;
        }
        #endregion

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strShowColumns"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="condition"></param>
        /// <param name="orderby"></param>
        /// <param name="recordCount"></param>
        /// <param name="strSqlConn"></param>
        /// <returns></returns>
        public DataSet GetListByPager(string strTableName, string strShowColumns, int pageNumber, int pageSize, string condition, string orderby, out int recordCount,string strSqlConn)
        {
            DataSet ds = new DataSet();
            string strStoreProcedure = "DataBasePager";
            SqlDatabase odbDataBase = new SqlDatabase(strSqlConn);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
            odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, strTableName);
            odbDataBase.AddInParameter(odbCommand, "@ShowColumns", System.Data.DbType.String, strShowColumns);
            odbDataBase.AddInParameter(odbCommand, "@pageNumber", System.Data.DbType.Int32, pageNumber);
            odbDataBase.AddInParameter(odbCommand, "@pageSize", System.Data.DbType.Int32, pageSize);
            odbDataBase.AddInParameter(odbCommand, "@condition", System.Data.DbType.String, condition);
            odbDataBase.AddInParameter(odbCommand, "@orderby", System.Data.DbType.String, orderby);
            odbDataBase.AddOutParameter(odbCommand, "@recordCount", System.Data.DbType.Int32, 0);
            return GetDataSet(odbDataBase, odbCommand, out recordCount);
        }
        /// <summary>
        /// 泛型 获取列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strSelectQuery"></param>
        /// <param name="strSqlConn"></param>
        /// <returns></returns>
        public List<T> GetListByCondition<T>(string strSelectQuery ,string strSqlConn)
        {
            SqlDatabase odbDataBase = new SqlDatabase(strSqlConn);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSelectQuery);
            List<T> olstInfo = new List<T>();
            T oInfo = default(T);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = System.Activator.CreateInstance<T>();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
                CloseConnection(odbCommand);//关闭数据库连接
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        /// <summary>
        /// 通过IDataReader的方式读取数据
        /// </summary>
        /// <param name="strSelectQuery"></param>
        /// <param name="strSqlConn"></param>
        /// <returns></returns>
        public DataTable GetDatatablebyReader(string strSelectQuery, string strSqlConn)
        {
            DataTable oDataTable = new DataTable();
            SqlDatabase odbDataBase = new SqlDatabase(strSqlConn);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSelectQuery);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    oDataTable.Load(reader);
                }
                CloseConnection(odbCommand);//关闭数据库连接
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oDataTable;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strSqlString"></param>
        /// <param name="strDBConnection"></param>
        /// <returns></returns>
        public EnumSubmitResult ExeSQLQuery(string strSqlString,string strDBConnection)
        {
            SqlDatabase odbDataBase = new SqlDatabase(strDBConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
                CloseConnection(odbCommand);//关闭数据库连接
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }

        /// <summary>
        /// 获取 SqlDatabase
        /// </summary>
        /// <returns></returns>
        public SqlDatabase GetSqlDatabase(string strSqlConn)
        {
            return new SqlDatabase(strSqlConn);
        }

        /// <summary>
        /// 根据  SqlDatabase  DbCommand 执行
        /// </summary>
        /// <param name="oSqlDatabase"></param>
        /// <param name="oDbCommand"></param>
        /// <returns></returns>
        public EnumSubmitResult ExeByCommandSqlDatabase(SqlDatabase oSqlDatabase, DbCommand oDbCommand)
        {
            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                oSqlDatabase.ExecuteNonQuery(oDbCommand);
                enmResult = EnumSubmitResult.Success;
                CloseConnection(oDbCommand);//关闭数据库连接
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="oDbCommand"></param>
        private void CloseConnection(DbCommand oDbCommand)
        {
            if (oDbCommand != null && oDbCommand.Connection != null)
                oDbCommand.Connection.Close();
        }

    }
}
